Imports System.Data
Imports System.Data.SqlClient

Public Class SQL
    Public Shared Function SQLConnect(ByVal tcConnectionString As String) As Object
        Try
            Dim connection As New SqlConnection(tcConnectionString)
            connection.Open()
            Return connection
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Shared Function SQLDisConnect(ByRef toConn As SqlConnection) As Object
        Dim obj As Object = Nothing
        toConn.Close()
        Return obj
    End Function

    Public Shared Function SQLExecute(ByRef toConn As SqlConnection, ByVal tcSQL As String) As Object
        Try
            If (toConn.State <> ConnectionState.Open) Then
                toConn.Open()
            End If
        Catch ex As Exception
        End Try
        Dim command As New SqlCommand(tcSQL, toConn)
        command.CommandTimeout = toConn.ConnectionTimeout
        Return command.ExecuteNonQuery
    End Function

    Public Shared Function SQLRetrieve(ByRef toConn As SqlConnection, ByVal tcSQL As String, ByVal tcAlias As String, ByRef ds As DataSet) As Object
        Dim oDa As SqlDataAdapter
        oDa = New SqlDataAdapter(tcSQL, toConn)
        Return oDa.Fill(ds, tcAlias)
    End Function

    Public Shared Function SQLInsert(ByRef toConn As SqlConnection, ByVal cTable As String, ByVal toRow As DataRow) As Object
        Dim num As Integer
        Dim ds As New DataSet
        SQL.SQLRetrieve((toConn), ("SELECT * FROM " & cTable & " WHERE 1 = 0"), "cStruct", (ds))
        Dim str As String = ("INSERT INTO " & cTable & "(")
        Dim flag As Boolean = False
        Dim num3 As Integer = (ds.Tables.Item("cStruct").Columns.Count - 1)
        num = 0
        Do While (num <= num3)
            If SQL.IsField(toRow, ds.Tables.Item("cStruct").Columns.Item(num).ColumnName) And Not (String.Compare(ds.Tables.Item("cStruct").Columns.Item(num).ColumnName, "rowguid") = 0) Then
                str = str + IIf(Not flag, "", ",") + ds.Tables.Item("cStruct").Columns.Item(num).ColumnName
                flag = True
            End If
            num += 1
        Loop
        str = (str & ") VALUES (")
        flag = False
        Dim num2 As Integer = (ds.Tables.Item("cStruct").Columns.Count - 1)
        num = 0
        Do While (num <= num2)
            If SQL.IsField(toRow, ds.Tables.Item("cStruct").Columns.Item(num).ColumnName) And Not (String.Compare(ds.Tables.Item("cStruct").Columns.Item(num).ColumnName, "rowguid") = 0) Then
                str = str + IIf(Not flag, "", ",") + SQL.ConvertVS2SQLType(toRow.Item(ds.Tables.Item("cStruct").Columns.Item(num).ColumnName), ds.Tables.Item("cStruct").Columns.Item(num).DataType.ToString)
                flag = True
            End If
            num += 1
        Loop
        str = (str & ")")
        ds = Nothing
        Return SQL.SQLExecute((toConn), str)
    End Function

    Public Shared Function SQLReInsert(ByRef toConn As SqlConnection, ByVal cTable As String, ByVal toRow As DataRow, ByVal cFieldKey As String, ByVal userName As String) As Object
        Dim ds As New DataSet
        Dim cKey As String = cFieldKey + " =''"
        SQL.SQLRetrieve((toConn), ("SELECT * FROM " & cTable & " WHERE 1 = 0"), "cStruct", (ds))
        Dim str As String = ("UPDATE " & cTable & " SET ")
        Dim flag As Boolean = False
        Dim num2 As Integer = (ds.Tables.Item("cStruct").Columns.Count - 1)
        Dim i As Integer = 0
        Do While (i <= num2)
            If SQL.IsField(toRow, ds.Tables.Item("cStruct").Columns.Item(i).ColumnName) And Not (IsExclusiveField(ds.Tables.Item("cStruct").Columns.Item(i).ColumnName)) Then
                str = str + IIf(Not flag, "", ",") + ds.Tables.Item("cStruct").Columns.Item(i).ColumnName + "=" + SQL.ConvertVS2SQLType(toRow.Item(ds.Tables.Item("cStruct").Columns.Item(i).ColumnName), ds.Tables.Item("cStruct").Columns.Item(i).DataType.ToString)
                flag = True
                If String.Compare(ds.Tables.Item("cStruct").Columns.Item(i).ColumnName, cFieldKey) = 0 Then
                    cKey = ds.Tables.Item("cStruct").Columns.Item(i).ColumnName + "=" + SQL.ConvertVS2SQLType(toRow.Item(ds.Tables.Item("cStruct").Columns.Item(i).ColumnName), ds.Tables.Item("cStruct").Columns.Item(i).DataType.ToString)
                End If
            End If
            i += 1
        Loop

        str += ", datetime2='" + GetCurrentDateTime() + "'"
        str += ", user2='" + userName + "'"
        str += ", datetime0='" + GetCurrentDateTime() + "'"
        str += ", user0='" + userName + "'"
        str += ", sync='0'"
        str += ", status='N'"

        If cKey <> "" Then
            str = (str & " WHERE " & cKey)
        End If
        ds = Nothing

        Return SQL.SQLExecute((toConn), str)
    End Function

    Public Shared Function SQLInsert(ByRef toConn As SqlConnection, ByVal cTable As String, ByVal toRow As DataRow, ByVal userName As String) As Object
        Dim num As Integer
        Dim ds As New DataSet
        SQL.SQLRetrieve((toConn), ("SELECT * FROM " & cTable & " WHERE 1 = 0"), "cStruct", (ds))
        Dim str As String = ("INSERT INTO " & cTable & "(")
        Dim flag As Boolean = False
        Dim num3 As Integer = (ds.Tables.Item("cStruct").Columns.Count - 1)
        num = 0
        Do While (num <= num3)
            If SQL.IsField(toRow, ds.Tables.Item("cStruct").Columns.Item(num).ColumnName) And Not (IsExclusiveField(ds.Tables.Item("cStruct").Columns.Item(num).ColumnName)) Then
                str = str + IIf(Not flag, "", ",") + ds.Tables.Item("cStruct").Columns.Item(num).ColumnName
                flag = True
            End If
            num += 1
        Loop       

        str = str + ", datetime2, user2, datetime0, user0, sync, status "
        str = (str & ") VALUES (")
        flag = False
        Dim num2 As Integer = (ds.Tables.Item("cStruct").Columns.Count - 1)
        num = 0
        Do While (num <= num2)
            If SQL.IsField(toRow, ds.Tables.Item("cStruct").Columns.Item(num).ColumnName) And Not (IsExclusiveField(ds.Tables.Item("cStruct").Columns.Item(num).ColumnName)) Then
                str = str + IIf(Not flag, "", ",") + SQL.ConvertVS2SQLType(toRow.Item(ds.Tables.Item("cStruct").Columns.Item(num).ColumnName), ds.Tables.Item("cStruct").Columns.Item(num).DataType.ToString)
                flag = True
            End If
            num += 1
        Loop

        str = str + ",'" + GetCurrentDateTime() + "','" + userName + "'"
        str = str + ",'" + GetCurrentDateTime() + "','" + userName + "','0','N' "
        str = (str & ")")
        ds = Nothing        
        Return SQL.SQLExecute((toConn), str)
    End Function

    Public Shared Function SQLUpdate(ByRef toConn As SqlConnection, ByVal cTable As String, ByVal toRow As DataRow, ByVal cKey As String, ByVal userName As String) As Object
        Dim ds As New DataSet
        SQL.SQLRetrieve((toConn), ("SELECT * FROM " & cTable & " WHERE 1 = 0"), "cStruct", (ds))
        Dim str As String = ("UPDATE " & cTable & " SET ")
        Dim flag As Boolean = False
        Dim num2 As Integer = (ds.Tables.Item("cStruct").Columns.Count - 1)
        Dim i As Integer = 0
        Do While (i <= num2)
            If SQL.IsField(toRow, ds.Tables.Item("cStruct").Columns.Item(i).ColumnName) And Not (IsExclusiveField(ds.Tables.Item("cStruct").Columns.Item(i).ColumnName)) Then
                str = str + IIf(Not flag, "", ",") + ds.Tables.Item("cStruct").Columns.Item(i).ColumnName + "=" + SQL.ConvertVS2SQLType(toRow.Item(ds.Tables.Item("cStruct").Columns.Item(i).ColumnName), ds.Tables.Item("cStruct").Columns.Item(i).DataType.ToString)
                flag = True
            End If
            i += 1
        Loop

        str += ", datetime2='" + GetCurrentDateTime() + "'"
        str += ", user2='" + userName + "'"
        str += ", sync='0'"
        str += ", status='N'"
        If cKey <> "" Then
            str = (str & " WHERE " & cKey)
        End If
        ds = Nothing
        Return SQL.SQLExecute((toConn), str)
    End Function

    Public Shared Function SQLUpdate(ByRef toConn As SqlConnection, ByVal cTable As String, ByVal toRow As DataRow, ByVal cKey As String) As Object
        Dim ds As New DataSet
        SQL.SQLRetrieve((toConn), ("SELECT * FROM " & cTable & " WHERE 1 = 0"), "cStruct", (ds))
        Dim str As String = ("UPDATE " & cTable & " SET ")
        Dim flag As Boolean = False
        Dim num2 As Integer = (ds.Tables.Item("cStruct").Columns.Count - 1)
        Dim i As Integer = 0
        Do While (i <= num2)
            If SQL.IsField(toRow, ds.Tables.Item("cStruct").Columns.Item(i).ColumnName) And Not (String.Compare(ds.Tables.Item("cStruct").Columns.Item(i).ColumnName, "rowguid") = 0) Then
                str = str + IIf(Not flag, "", ",") + ds.Tables.Item("cStruct").Columns.Item(i).ColumnName + "=" + SQL.ConvertVS2SQLType(toRow.Item(ds.Tables.Item("cStruct").Columns.Item(i).ColumnName), ds.Tables.Item("cStruct").Columns.Item(i).DataType.ToString)
                flag = True
            End If
            i += 1
        Loop
        If cKey <> "" Then
            str = (str & " WHERE " & cKey)
        End If
        ds = Nothing
        Return SQL.SQLExecute((toConn), str)
    End Function

    Public Shared Function SQLDelete(ByRef toConn As SqlConnection, ByVal cTable As String, ByVal cKey As String) As Object
        Dim tcSQL As String = ("DELETE " & cTable)
        If cKey <> "" Then
            tcSQL = (tcSQL & " WHERE " & cKey)
        End If
        Return SQL.SQLExecute((toConn), tcSQL)
    End Function

    Public Shared Function SQLDelete(ByRef toConn As SqlConnection, ByVal cTable As String, ByVal cKey As String, ByVal userName As String) As Object
        Dim tcSQL As String = ("UPDATE " & cTable & " SET ")
        tcSQL += "user2='" + userName + "'"
        tcSQL += ", datetime2='" + GetCurrentDateTime() + "'"
        tcSQL += ", sync='0'"
        tcSQL += ", status='D'"
        If cKey <> "" Then
            tcSQL = (tcSQL & " WHERE " & cKey)
        End If
        Return SQL.SQLExecute((toConn), tcSQL)
    End Function

    Public Shared Function IsField(ByVal toRow As DataRow, ByVal cField As String) As Boolean
        Dim obj As Object = True
        Try
            Dim oValue As Object = toRow.Item(cField)
            obj = Not (oValue Is Nothing)
        Catch ex As Exception
            obj = False
        End Try
        Return obj
    End Function


    Public Shared Function ConvertVS2SQLType(ByVal eValue As Object, ByVal sType As String) As Object
        If sType = "" Then
            sType = eValue.GetType.ToString
        End If
        Dim sLeft As String = sType
        If sLeft = "System.Char" Or sLeft = "System.String" Then
            If IsDBNull(eValue) Then
                Return "''"
            End If
            Return SQL.ConvertQMC2SQL(eValue)
        End If
        If sLeft = "System.Byte" Or sLeft = "System.sByte" Or sLeft = "System.Int16" Or sLeft = "System.Int32" Or sLeft = "System.Int64" Or sLeft = "System.Decimal" Or sLeft = "System.Double" Or sLeft = "System.Integer" Or sLeft = "System.Long" Or sLeft = "System.Single" Then
            If IsDBNull(eValue) Then
                Return "0"
            End If
            Return eValue.ToString
        End If
        If sLeft = "System.Boolean" Then
            If IsDBNull(eValue) Then
                Return "0"
            End If
            Return IIf(eValue, "1", "0")
        End If
        If sLeft = "System.Date" Then
            If IsDBNull(eValue) Then
                Return "Convert(DateTime, NULL)"
            End If
            Return ("'" & Strings.Format(eValue, "yyyyMMdd") & "'")
        End If
        If sLeft = "System.DateTime" Then
            If IsDBNull(eValue) Then
                Return "Convert(DateTime, NULL)"
            End If
            Return ("'" & Strings.Format(eValue, "yyyyMMdd HH:mm:ss") & "'")
        End If
        If IsDBNull(eValue) Then
            Return "Convert(DateTime, NULL)"
        End If
        Return ("'" & Strings.Format(eValue, "yyyyMMdd HH:mm:ss") & "'")
    End Function

    Public Shared Function ConvertQMC2SQL(ByVal cSQLValue As Object) As Object
        If Strings.InStr(cSQLValue, "'") <= 0 Then
            Return " N'" + cSQLValue + "'"
        End If
        Return Nothing
    End Function

    Public Shared Function GetRow(ByRef toConn As SqlConnection, ByVal cTable As String, ByVal cKey As String) As DataRow
        Dim tcSQL As String = ("Select * FROM " & cTable & " WHERE " & cKey)
        Dim ds As New DataSet
        SQL.SQLRetrieve((toConn), tcSQL, cTable, (ds))
        If ds.Tables.Count = 0 Then
            Return Nothing
        Else
            If ds.Tables(0).Rows.Count > 0 Then
                Return ds.Tables(0).Rows(0)
            Else
                Return Nothing
            End If
        End If
    End Function

    Public Shared Function GetValue(ByRef toConn As SqlConnection, ByVal cTable As String, ByVal cField As String, ByVal cKey As String) As Object
        Dim ds As New DataSet
        Dim tcSQL As String = String.Concat(New String() {"Select ", cField, " AS xValue FROM ", cTable, " WHERE ", cKey})
        SQL.SQLRetrieve((toConn), tcSQL, cTable, (ds))
        If ds.Tables.Count > 0 Then
            If ds.Tables(0).Rows.Count = 0 Then
                Return Nothing
            Else
                Return ds.Tables(0).Rows(0).Item("xValue")
            End If
        Else
            Return Nothing
        End If
    End Function

    Public Shared Function isValidCode(ByVal cn As SqlConnection, ByVal cValue As String, ByVal cField As String, ByVal cTable As String, ByVal cAction As String) As Object
        Dim str As String
        Dim str2 As String
        If cAction = "New" Then
            str = ""
        Else
            str = String.Concat(New String() {"(", cField, " <> '", Strings.Trim(cAction), "') AND "})
        End If

        str2 = (String.Concat(New String() {"IF EXISTS(SELECT * FROM ", cTable, " WHERE ", str, cField}) & " = N'" & Strings.Trim(cValue) & "')")

        str2 = (str2 & " SELECT 0 AS Value ELSE SELECT 1 AS Value")
        Dim ds As New DataSet
        SQL.SQLRetrieve((cn), str2, "Value", (ds))
        Dim flag As Boolean = IIf(ds.Tables.Item("Value").Rows.Item(0).Item("Value") = 1, True, False)
        ds = Nothing
        Return flag
    End Function

    Public Shared Function isDeleted(ByVal cn As SqlConnection, ByVal cValue As String, ByVal cField As String, ByVal cTable As String, ByVal cAction As String) As Object
        Dim str As String
        Dim str2 As String
        'If cAction = "New" Then
        str = "status ='D' AND "
        'Else
        '   str = String.Concat(New String() {"(", cField, " <> '", Strings.Trim(cAction), "') AND "})
        'End If

        str2 = (String.Concat(New String() {"IF EXISTS(SELECT * FROM ", cTable, " WHERE ", str, cField}) & " = '" & Strings.Trim(cValue) & "')")

        str2 = (str2 & " SELECT 1 AS Value ELSE SELECT 0 AS Value")
        Dim ds As New DataSet
        SQL.SQLRetrieve((cn), str2, "Value", (ds))
        Dim flag As Boolean = IIf(ds.Tables.Item("Value").Rows.Item(0).Item("Value") = 1, True, False)
        ds = Nothing
        Return flag
    End Function

    Public Shared Function IsExclusiveField(ByVal cField As String) As Boolean
        Dim obj As Object = False
        If (String.Compare(cField, "datetime2") = 0) Or (String.Compare(cField, "user2") = 0) Or (String.Compare(cField, "sync") = 0) Or (String.Compare(cField, "user0") = 0) Or (String.Compare(cField, "datetime0") = 0) Or (String.Compare(cField, "status") = 0) Or (String.Compare(cField, "rowguid") = 0) Then
            obj = True
        End If
        Return obj
    End Function

    Public Shared Function GetCurrentDateTime() As String
        Dim dateTimeInfo As DateTime = DateTime.Now
        Dim iYear As Integer = dateTimeInfo.Year
        Dim iMonth As Integer = dateTimeInfo.Month
        Dim iDate As Integer = dateTimeInfo.Date.Day
        Dim iHour As Integer = dateTimeInfo.Hour
        Dim iMin As Integer = dateTimeInfo.Minute
        Dim iSecond As Integer = dateTimeInfo.Second
        Dim sday As String = String.Empty
        sday = iYear & "-" & iMonth & "-" & iDate & " " & iHour & ":" & iMin & ":" & iSecond
        Return sday
    End Function
End Class
